In modem computing environments, databases are usually maintained on a central computer (i.e. a server) that is accessible by a plurality of computer workstations (i.e. clients) connected via a network. The clients have a client database application which is used to access data from a particular database on the server. Each client may be interested in a portion of data in a particular database and these portions of data may overlap for different clients. Accordingly, the server has a database management system (DBMS), such as DB2™ made by IBM™, that is responsible for data storage and concurrency control so that several clients can share the same database without interfering with one another or compromising data integrity. The DBMS is also responsible for recovery features to protect and restore data integrity upon server failures. The DBMS operates using a query database language, such as SQL (Structured Query Language). The client database application may interface with the DBMS using SQL or standard Open Database Connectivity (ODBC) database drivers, such as ODBC 3.0 developed by Microsoft™.
Many databases are implemented as relational databases in which the data contained in the database is organized as though it were formatted into a series of tables. These tables have a series of columns (i.e. fields) which identify the type of information that the table contains and a series of rows (i.e. records) which contain values for each field relating to an entry in the database. One or more of the fields may be used as an index or a key to uniquely identify a record for searching purposes. For example, a table in a database may show employee statistics for a particular company. The fields in the table may be employee name, employee number, number of years of service, salary, available vacation days and the like. The records in the table would contain values for these fields for the employees who work for the company. The employee number field may be used as an index for searching purposes.
A client database application obtains data from a database by requesting a search of the database via a query. The query would ask the DBMS to search certain tables in a particular database for records having fields that satisfy certain criteria. The DBMS then custom builds an access plan for searching the database according to the query and, through the use of a run-time engine, executes a search of the database according to the access plan and generates results of the query in a separate data structure known as a result set or an active set. When the run-time engine of the DBMS locates records which satisfy the query that is made by the client database application and populates the result set, the records are said to be materialized in the result set by the run-time engine. The size of the result set indicates the number of records which satisfy the query. The result set is then communicated to the client database application that submitted the query.
The result set is not formatted to allow individual records to be accessed by the client database application. To remedy this problem, as is well known in the art, a data structure called a database cursor, also known as a cursor, is created to view the records in the result set. When the located records are transferred to the database cursor at the client database application, the records are said to be materialized by the database cursor. Accordingly, the database cursor contains all of the records of the result set and permits the records to be accessed one at a time (also known as scrolling through the result set). The database cursor also maintains a position indicating the current record that the database cursor is pointing at. The database cursor may be used to access and/or modify a record in the result set.
The database cursor is usually created and maintained by the client database application at the time at which the client database application generates a query. The database cursor is built having a structure that is commensurate with the result set that is expected in response to the query. Implementing a database cursor at the client database application permits the application to set data isolation (i.e. data locking) and concurrency levels within the database that the application is interfacing with. Concurrency refers to the ability of the DBMS to permit various clients access to the same database as well as reflecting changes to the data contained in the database to a particular client. Data isolation refers to the ability of the DBMS to inhibit various clients from modifying data in the database. Generally, a high degree of concurrency is correlated with a lower degree of data isolation and vice-versa.
There are various degrees of data locks that are defined in DB2™ such as X-locks, U-locks and S-locks. An X-lock is the most restrictive type of data lock. An X-lock means that a client has an uncommitted change pending on a record in the database. The change could be an update to one of the fields in the record or a deletion of the record. At this time, other clients can not acquire any lock on this record. A U-lock is acquired by clients that may modify a record. During this time, other clients can not acquire a lock on this record. An S-lock is acquired by clients that may not modify a record (i.e., the access is read only).
Unfortunately, database cursors and the method of searching and materializing records in response to a query have certain disadvantages. For instance, if the result set is large then materialization by the run-time engine and the database cursor can be time intensive. This is particularly inefficient in terms of time and resources for the situations in which a user of the client database application scrolls over only a fraction of the records in the result set which is quite common.
In addition, there are also concurrency and data isolation issues since some of the located records may have data locks placed on them by other client database applications. In this case, the DBMS would have to wait until all of the record-level locks are released before sending the result set to the client database application. A user of the client database application may use a UR (i.e. Uncommitted Read) command to avoid this wait and only read (i.e. not update or delete) the records in the result set. However, the user would be exposed to uncommitted (or dirty) data in the result set since some records in the result set may have been updated by other client database applications but yet not committed to the database.
Furthermore, client database applications usually specify timeout criteria which result in the cancellation of a query if the DBMS is taking an excessive amount of time to return a result. If the DBMS has to wait excessively for the locks on records contained in the result to be removed, the client database application may cancel the query before the client database application receives any data which is an inefficient use of system resources. Accordingly, there is a need for an improved method and system for materializing the results of a database query to a client that reduces the materialization and transmission of unwanted search results and improves concurrency issues for server-client networks.